﻿
use ASIVeste

go

/********************************************************************************************
*****
*****
*********************************************************************************************/


if(OBJECT_ID('totalProductsToOrder') is not null)
	DROP PROCEDURE totalProductsToOrder
	
go

create procedure totalProductsToOrder(@order1 bit, @order2 bit)
as
	select COUNT(*)
	from Product
	where stock <= minStock and (ordered = @order1 or ordered = @order2)
	
go


/********************************************************************************************
*****
*****
*********************************************************************************************/



if(OBJECT_ID('productsToOrder') is not null)
	DROP PROCEDURE productsToOrder
go

create procedure productsToOrder(@page int, @recordsPerPage int, @order1 bit, @order2 bit)
as
begin

	declare @firstrow int
	declare @lastrow int

	set @firstrow = ( @page - 1) * @recordsPerPage + 1
	set @lastrow = (@page - 1) * @recordsPerPage + @recordsPerPage
	
	select productCode, minStock, type, stock, ordered,
		supplierNumber, supplierName, supplierAddress
	from(
		select code as productCode, minStock, type, stock, ordered,
			   number as supplierNumber, name as supplierName, address as supplierAddress,
			   ROW_NUMBER() over(order by code) as RowNumber
		from Product join Supplier
		on(number = supplier)
		where stock <= minStock and (ordered = @order1 or ordered = @order2)
	) as t
	where RowNumber between  @firstrow and @lastrow
	
end

go

/********************************************************************************************
*****
*****
*********************************************************************************************/



if(OBJECT_ID('orderReceived') is not null)
	DROP PROCEDURE orderReceived
go

create procedure orderReceived(@productCode int, @amount int)
as
begin

	update Product set stock = stock + @amount, ordered = 0
	where code = @productCode

	select type from Product where code = @productCode
end

go

/********************************************************************************************
*****
*****
*********************************************************************************************/



if(OBJECT_ID('productOrdered') is not null)
	DROP PROCEDURE productOrdered
go

create procedure productOrdered(@productCode int)
as
begin

	update Product set ordered = 1
	where code = @productCode

end

go

